package com.anjie.powerproject.repository;

import com.anjie.powerproject.entity.MAverage;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface MAverageRepository extends JpaRepository<MAverage, Long> {

    @Query(nativeQuery = true, value = "select (@i \\:= @i + 1) as id, CAST(DATE_FORMAT(sdate, '%Y') as signed) as `year`, CAST(DATE_FORMAT(sdate, '%m') as signed) as `month` , CAST(avg(temphigh) as decimal(9, 2)) as m_average_max, CAST(avg(templow) as decimal(9, 2)) as m_average_min from (select @i \\:= 0) r, weather_history_daily group by DATE_FORMAT(sdate, '%Y%m')")
    List<MAverage> getMAverage();
}
